package org.gulugulubiao.service.Impl;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import lombok.extern.slf4j.Slf4j;
import org.gulugulubiao.constant.MessageConstant;
import org.gulugulubiao.dto.ProblemPageQueryDTO;
import org.gulugulubiao.entity.JudgeResult;
import org.gulugulubiao.entity.Problem;
import org.gulugulubiao.exception.*;
import org.gulugulubiao.mapper.ProblemMapper;
import org.gulugulubiao.result.PageResult;
import org.gulugulubiao.result.Result;
import org.gulugulubiao.service.ProblemService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.gulugulubiao.constant.MessageConstant.ANSWER_SQLERROR;

@Service
@Slf4j
public class ProblemServiceImpl implements ProblemService {
    @Autowired
    private ProblemMapper problemMapper;

    // 获取数据库连接
    private static final String url = "jdbc:mysql://120.79.72.63:3307/sqlOnline";
    private static final String username = "root";
    private static final String password = "sqlOJ2025$$02qweokn!@#&1das";

    //设置查询的表语句
    private String problemSql = "select * from problem";
    private String studentSql = "select * from students";
    private String courseSql = "select * from courses";
    private String enrollmentsSql = "select * from enrollments";


    /**
     * 判断答案
     */
    @Override
    public JudgeResult problemJudge(Integer problemId, String inputAnswer) {
        JudgeResult judgeResult = new JudgeResult();
        //查询problem表获取信息
        Problem problem = problemMapper.problemJudge(problemId);

        //获得题目标签，1为查，2为改，3为删，4为增
        Integer tagId = problem.getTagId();

        //如果上传的答案与数据库存储的答案一致则直接返回正确
        if (inputAnswer.equals(problem.getAnswer())) {
            judgeResult.setAnswer(problem.getAnswer());
            judgeResult.setFlag(true);
            return judgeResult;
        }

        if (tagId.equals(1)) {
            try {
                return judgeSelect(inputAnswer, problem.getAnswer());
            } catch (SQLException | ClassNotFoundException e) {
                e.printStackTrace();
            }
        }

        try {
            //连接数据库
            Connection conn = DriverManager.getConnection(url, username, password);

            // 关闭事务自动提交，改为手动提交
            conn.setAutoCommit(false);

            // 获取预编译SQL语句对象
            Statement statement = conn.createStatement();

            //获取执行提交语句的表
            List<Map<String, Object>> inputResultList = execute(conn, statement, inputAnswer);
            //获取执行正确答案语句的表
            List<Map<String, Object>> rightResultList = execute(conn, statement, problem.getAnswer());

            //判断两张表是否完全一样
            judgeResult.setAnswer(problem.getAnswer());
            if (compareResultLists(inputResultList, rightResultList)) {
                judgeResult.setFlag(true);
            } else {
                judgeResult.setFlag(false);
            }

            System.out.println(judgeResult);

        } catch (Exception e) {
            //SQL语法有误时直接返回
            judgeResult = new JudgeResult();
            judgeResult.setFlag(false);
            judgeResult.setAnswer(problem.getAnswer());
            return judgeResult;
        }
        return judgeResult;
    }

    private JudgeResult judgeSelect(String inputAns, String rightAns) throws ClassNotFoundException, SQLException, WrongAnswerWithBadGrammarException {
        // 注册驱动，新版驱动类（如果显式加载驱动）
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取连接
        Connection connection = DriverManager.getConnection(url, username, password);
        // 禁用自动提交，手动管理事务
        connection.setAutoCommit(false);
        // 定义要执行的sql
        String sql = inputAns;

        // 获取执行SQL的对象
        Statement statement = connection.createStatement();

        JudgeResult judgeResult = new JudgeResult();
        judgeResult.setAnswer(rightAns);

        ResultSet resultSet;
        // 执行SQL
        try {
            resultSet = statement.executeQuery(sql); // 执行查询
        } catch (Exception exception) {
            // 定义语法错误也是错误答案，msg提示前端语法错误，code置0，data返回正确答案
            // throw new WrongAnswerWithBadGrammarException("用户输入的SQL有语法错误");
            judgeResult.setFlag(false);
            return judgeResult;
        }

        // 获取元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount(); // 获取列数
        List<Map<String, Object>> inputResultList = new ArrayList<>();

        // 动态处理结果
        while (resultSet.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i); // 获取列名
                Object value = resultSet.getObject(i); // 获取列值
                rowMap.put(columnName, value); // 存储列名和值
            }
            inputResultList.add(rowMap); // 将当前行数据添加到结果列表
        }

        // 执行正确sql
        sql = rightAns;
        resultSet = statement.executeQuery(sql); // 执行查询
        // 获取元数据
        metaData = resultSet.getMetaData();
        // 获取列数
        columnCount = metaData.getColumnCount();
        List<Map<String, Object>> rightResultList = new ArrayList<>();
        // 动态处理结果
        while (resultSet.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i); // 获取列名
                Object value = resultSet.getObject(i); // 获取列值
                rowMap.put(columnName, value); // 存储列名和值
            }
            rightResultList.add(rowMap); // 将当前行数据添加到结果列表
        }


        try {
            statement.close();
        } catch (SQLException e) {
            log.error("关闭 statement 时出错", e);
        }

        try {
            connection.close();
        } catch (SQLException e) {
            log.error("关闭 connection 时出错", e);
        }

        judgeResult.setFlag(compareResultLists(inputResultList, rightResultList));

        return judgeResult;
    }


    //执行数据库语句，获得执行后的整张表
    public List<Map<String, Object>> execute(Connection conn, Statement statement, String str) {
        try {
            List<Map<String, Object>> inputResultList = new ArrayList<>();
            // 执行SQL操作
            statement.execute(str);

            //获取students表
            String student = studentSql;
            ResultSet studentlist = statement.executeQuery(student);
            // 获取元数据
            ResultSetMetaData metaData1 = studentlist.getMetaData();
            int columnCount1 = metaData1.getColumnCount(); // 获取列数
            // 动态处理结果
            while (studentlist.next()) {
                Map<String, Object> rowMap = new HashMap<>();
                for (int i = 1; i <= columnCount1; i++) {
                    String columnName = metaData1.getColumnName(i); // 获取列名
                    Object value = studentlist.getObject(i); // 获取列值
                    rowMap.put(columnName, value); // 存储列名和值
                }
                inputResultList.add(rowMap); // 将当前行数据添加到结果列表
            }

            //获取courses表
            String course = courseSql;
            ResultSet courselist = statement.executeQuery(course);
            ResultSetMetaData metaData2 = courselist.getMetaData();
            int columnCount2 = metaData2.getColumnCount(); // 获取列数
            // 动态处理结果
            while (courselist.next()) {
                Map<String, Object> rowMap = new HashMap<>();
                for (int i = 1; i <= columnCount2; i++) {
                    String columnName = metaData2.getColumnName(i); // 获取列名
                    Object value = courselist.getObject(i); // 获取列值
                    rowMap.put(columnName, value); // 存储列名和值
                }
                inputResultList.add(rowMap); // 将当前行数据添加到结果列表
            }

            //获取enrollments表
            String enrollments = enrollmentsSql;
            ResultSet enrollmentslist = statement.executeQuery(enrollments);
            ResultSetMetaData metaData3 = enrollmentslist.getMetaData();
            int columnCount3 = metaData3.getColumnCount(); // 获取列数
            // 动态处理结果
            while (enrollmentslist.next()) {
                Map<String, Object> rowMap = new HashMap<>();
                for (int i = 1; i <= columnCount3; i++) {
                    String columnName = metaData3.getColumnName(i); // 获取列名
                    Object value = enrollmentslist.getObject(i); // 获取列值
                    rowMap.put(columnName, value); // 存储列名和值
                }
                inputResultList.add(rowMap); // 将当前行数据添加到结果列表
            }

            // 回滚事务
            conn.rollback();
            return inputResultList;

        } catch (SQLException e) {
            //抛出SQL语句错误异常
            throw new SQLErrorException(ANSWER_SQLERROR);
        }
    }

    // 比较两个List<Map<String, Object>>是否相等
    private boolean compareResultLists(List<Map<String, Object>> list1, List<Map<String, Object>> list2) {
        if (list1.size() != list2.size()) {
            return false; // 行数不同
        }

        // 逐行比较，假设行的顺序可以不同
        List<Map<String, Object>> copyOfList2 = new ArrayList<>(list2);

        for (Map<String, Object> row1 : list1) {
            boolean matchFound = false;
            for (Map<String, Object> row2 : copyOfList2) {
                if (row1.equals(row2)) {
                    copyOfList2.remove(row2);
                    matchFound = true;
                    break;
                }
            }
            if (!matchFound) {
                return false; // 没找到匹配的行
            }
        }
        return true; // 所有行均匹配
    }

    /**
     * 新增题目
     * @param problem
     */
    @Override
    public void add(Problem problem) {

        Integer id = problem.getId();

        Problem problemTemp = problemMapper.getProblem(id);

        //  处理异常情况
        //  id已存在
        if(problemTemp!= null) {
            throw new AddProblemFailException(MessageConstant.PROBLEM_ID_EXISTS);
        }

        problemMapper.add(problem);

    }

    /**
     * 显示id对应的题目
     *
     * @param problemId
     * @return
     */
    @Override
    public Problem getProblem(Integer problemId) {

        //  处理异常情况
        //  1. id不存在
        Problem problemTemp = problemMapper.getProblem(problemId);

        if(problemTemp == null) {
            throw new GetProblemFailException(MessageConstant.PROBLEM_ID_NOT_EXISTS);
        }
        //  2. 题目被锁定
        if(problemTemp.getStatus() == 0 ) {
            throw new GetProblemFailException(MessageConstant.PROBLEM_IS_LOCKED);
        }

        return problemMapper.getProblem(problemId);
    }

    /**
     * 删除id对应的题目
     *
     * @param problemId
     */
    @Override
    public void deleteProblem(Integer problemId) {

        //  处理异常情况
        //  id不存在
        Problem problemTemp = problemMapper.getProblem(problemId);

        if(problemTemp == null) {
            throw new DeleteProblemFailException(MessageConstant.PROBLEM_ID_NOT_EXISTS);
        }

        problemMapper.deleteProblem(problemId);
    }

    /**
     * 查询题目列表
     * @return
     */
    @Override
    public Result getAllProblem() {
        List<Problem> problemList = problemMapper.getAllProblem();
        return Result.success(problemList);
    }

    /**
     * 分页查询
     * @param problemPageQueryDTO
     * @return
     */
    @Override
    public PageResult getProblemList(ProblemPageQueryDTO problemPageQueryDTO) {

        // 开始分页查询
        int page = problemPageQueryDTO.getPage(); // 从实例中取得当前页
        int pageSize = problemPageQueryDTO.getPageSize(); // 从实例中取得每页大小
        PageHelper.startPage(page, pageSize);
        Page<Problem> problemPage = problemMapper.getProblemList(problemPageQueryDTO.getTagId());

        long total = problemPage.getTotal();
        List<Problem> problemList = problemPage.getResult();

        return new PageResult(total, problemList);

    }


}

